Skip Headers

Oracle® Database Advanced Replication Management API Reference 10g Release 2 (10.2) Part Number B14227-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 9 Managing Replication Objects and Queues

This chapter illustrates how to manage the replication objects and queues in your replication environment using the replication management API.

This chapter contains these topics:

- Altering a Replicated Object

- Modifying Tables without Replicating the Modifications

- Converting a LONG Column to a LOB Column in a Replicated Table

- Determining Differences Between Replicated Tables

- Managing the Deferred Transactions Queue

- Managing the Error Queue

## Altering a Replicated Object

As your database needs change, you might need to modify the characteristics of your replicated objects. It is important that you do not directly execute DDL to alter your replicated objects. Doing so might cause your replication environment to fail.

Note: If the logical structure of a master table is altered (for example, if a column name or type is changed), then all dependent materialized views must be rebuilt.

### Altering a Replicated Object in a Quiesced Master Group

Use the `ALTER_MASTER_REPOBJECT` procedure in the `DBMS_REPCAT` package to alter the characteristics of your replicated objects in a quiesced master group. From the example following, notice that you simply include the necessary DDL within the procedure call (see the `ddl_text` parameter).

If any master site is lower than 9.0.1 compatibility level, then you must use the following procedure. That is, the master group must be quiesced to modify a replicated object. You control the compatibility level of a database with the `COMPATIBLE` initialization parameter.

Meet the following requirements to complete these actions:

Executed As: Replication Administrator

Executed At: Master Definition Site

Replication Status: Quiesced

Complete the following steps to alter a replicated object in a quiesced master group.

Note: If your master site is running Oracle8i Database release 8.1.7 or later in a single master environment and you are making a safe change to a replicated object, then you might not need to quiesce the master group. See the "ALTER_MASTER_REPOBJECT Procedure" for information about when quiesce is not required. If you are viewing this document online, then you can copy the text from the "BEGINNING OF SCRIPT" line after this note to the "END OF SCRIPT" line into a text editor and then edit the text to create a script for your environment.

    /************************* BEGINNING OF SCRIPT ******************************

Step 1 Connect to the master definition site as the replication administrator.

    */

    SET ECHO ON

    SPOOL alter_rep_object.out

    CONNECT repadmin/repadmin@orc1.world

    /*

Step 2 If necessary, then quiesce the master group.

See the "ALTER_MASTER_REPOBJECT Procedure" for information about when quiesce is not required.

    */

    BEGIN
       DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
          gname => 'hr_repg');
    END;
    /

    /*

Step 3 In a separate SQL*Plus session, check the status of the master group you are quiescing.

Do not proceed until the group's status is `QUIESCED`.

To check the status, run the following query:

    SELECT GNAME, STATUS FROM DBA_REPGROUP;

    */

    PAUSE Press <RETURN> to continue when the master group's status is QUIESCED.

    /*

Step 4 Alter the replicated object.

    */

    BEGIN
       DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
          sname => 'hr',
          oname => 'employees',
          type => 'TABLE',
          ddl_text => 'ALTER TABLE hr.employees ADD (timestamp DATE)');
    END;
    /

    /*

Step 5 Regenerate replication support for the altered object.

    */

    BEGIN
        DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
          sname => 'hr',
          oname => 'employees',
          type => 'TABLE',
          min_communication => TRUE);
    END;
    /

    /*

Step 6 In a separate SQL*Plus session, check if DBA_REPCATLOG is empty.

Do not proceed until this view is empty.

Execute the following `SELECT` statement in another SQL*Plus session to monitor the `DBA_REPCATLOG` view:

    SELECT * FROM DBA_REPCATLOG WHERE GNAME = 'HR_REPG';

    */

    PAUSE Press <RETURN> to continue when DBA_REPCATLOG is empty.

    /*

Step 7 Resume replication activity.

    */

    BEGIN
       DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
          gname => 'hr_repg');
    END;
    /

    SET ECHO OFF

    SPOOL OFF

    /************************* END OF SCRIPT **********************************/

## Modifying Tables without Replicating the Modifications

You might have a situation in which you need to modify a replicated object, but you do not want this modification replicated to the other sites in the replication environment. For example, you might want to disable replication in the following situations:

- When you are using procedural replication to propagate a change, always disable row-level replication at the start of your procedure.

- You might need to disable replication in triggers defined on replicated tables to avoid replicating trigger actions multiple times. See "Ensuring that Replicated Triggers Fire Only Once".

- Sometimes when you manually resolve a conflict, you might not want to replicate this modification to the other copies of the table.

You might need to do this, for example, if you need to correct the state of a record at one site so that a conflicting replicated update will succeed when you reexecute the error transaction. Or, you might use an unreplicated modification to undo the effects of a transaction at its origin site because the transaction could not be applied at the destination site. In this example, you can use the Replication Management tool to delete the conflicting transaction from the destination site.

To modify tables without replicating the modifications, use the `REPLICATION_ON` and `REPLICATION_OFF` procedures in the `DBMS_REPUTIL` package. These procedures take no arguments and are used as flags by the generated replication triggers.

Note: To enable and disable replication, you must have the `EXECUTE` privilege on the `DBMS_REPUTIL` package.

### Disabling Replication

The `DBMS_REPUTIL.REPLICATION_OFF` procedure sets the state of an internal replication variable for the current session to `false`. Because all replicated triggers check the state of this variable before queuing any transactions, modifications made to the replicated tables that use row-level replication do not result in any queued deferred transactions.

Caution: Turning replication on or off affects only the current session. That is, other users currently connected to the same server are not restricted from placing committed changes in the deferred transaction queue.

If you are using procedural replication, then call `REPLICATION_OFF` at the start of your procedure, as shown in the following example. This ensures that the replication facility does not attempt to use row-level replication to propagate the changes that you make.

    CREATE OR REPLACE PACKAGE update_objects AS
      PROCEDURE update_emp(adjustment IN NUMBER);
    END;
    /

    CREATE OR REPLACE PACKAGE BODY update_objects AS
      PROCEDURE update_emp(adjustment IN NUMBER) IS
      BEGIN
       --turn off row-level replication for set update
       DBMS_REPUTIL.REPLICATION_OFF;
       UPDATE emp . . .;
       --reenable replication
       DBMS_REPUTIL.REPLICATION_ON;
      EXCEPTION WHEN OTHERS THEN
       . . .
       DBMS_REPUTIL.REPLICATION_ON;
      END;
    END;
    /

### Reenabling Replication

After resolving any conflicts, or at the end of your replicated procedure, be certain to call `DBMS_REPUTIL.REPLICATION_ON` to resume normal replication of changes to your replicated tables or materialized views. This procedure takes no arguments. Calling `REPLICATION_ON` sets the internal replication variable to `true`.

### Ensuring that Replicated Triggers Fire Only Once

If you have defined a replicated trigger on a replicated table, then you might need to ensure that the trigger fires only once for each change that you make. Typically, you only want the trigger to fire when the change is first made, and you do not want the remote trigger to fire when the change is replicated to the remote site.

You should check the value of the `DBMS_REPUTIL.FROM_REMOTE` package variable at the start of your trigger. The trigger should update the table only if the value of this variable is `false`.

Alternatively, you can disable replication at the start of the trigger and reenable it at the end of the trigger when modifying rows other than the one that caused the trigger to fire. Using this method, only the original change is replicated to the remote sites. Then the replicated trigger fires at each remote site. Any updates performed by the replicated trigger are not pushed to any other sites.

Using this approach, conflict resolution is not invoked. Therefore, you must ensure that the changes resulting from the trigger do not affect the consistency of the data.

## Converting a LONG Column to a LOB Column in a Replicated Table

LOB columns can be replicated, but `LONG` columns cannot be replicated. You can convert the datatype of a `LONG` column to a `CLOB` column and the datatype of a `LONG_RAW` column to a `BLOB` column.

Converting a `LONG` column to a LOB column can result in increased network bandwidth requirements because the data in such a column is replicated after conversion. Make sure you have adequate network bandwidth before completing the procedure in this section.

See Also: Oracle Database Application Developer's Guide - Large Objects for more information about applications and `LONG` to LOB conversion

Complete the following steps to convert a `LONG` column to a LOB column in a replicated table:

Step 1 Make sure the data in the LONG column is consistent at all replication sites.

If a table containing a `LONG` column is configured as a master table, then Oracle does not replicate changes to the data in the `LONG` column. Therefore, the data in the `LONG` column might not match at all of your replication sites. You must make sure the data in the `LONG` column matches at all master sites before proceeding.

Step 2 Connect to the master definition site as the replication administrator.

    CONNECT repadmin/repadmin@orc1.world

Step 3 If the replication status is normal, then change the status to quiesced.

    BEGIN

    DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
          gname => 'sales_mg');
    END;
    /

Step 4 Convert the LONG column to a LOB column.

    BEGIN

    DBMS_REPCAT.ALTER_MASTER_REPOBJECT (
          sname => 'staff',
          oname => 'positions',
          type => 'TABLE',
          ddl_text => 'ALTER TABLE staff.positions MODIFY (job_desc CLOB)');
    END;
    /

A `LONG_RAW` column can be converted to a `BLOB` column using a similar `ALTER` `TABLE` statement.

Step 5 Regenerate replication support for the altered master table.

    BEGIN

    DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT (
          sname => 'staff',
          oname => 'positions',
          type => 'TABLE',
          min_communication => TRUE);
    END;
    /

Step 6 Resume replication.

    BEGIN

    DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
          gname => 'sales_mg');
    END;
    /

Step 7 If materialized views are based on the altered table at any of the master sites, then rebuild these materialized views.

Rebuild materialized views if necessary.

## Determining Differences Between Replicated Tables

It is possible for the differences to arise in replicated tables. When administering a replication environment, you might want to check, periodically, whether the contents of two replicated tables are identical. The following procedures in the `DBMS_RECTIFIER_DIFF` package let you identify, and optionally rectify, the differences between two tables.

### Using the DIFFERENCES Procedure

The `DIFFERENCES` procedure compares two replicas of a table, and determines all rows in the first replica that are not in the second and all rows in the second that are not in the first. The output of this procedure is stored in two user-created tables. The first table stores the values of the missing rows, and the second table is used to indicate which site contains each row.

### Using the RECTIFY Procedure

The `RECTIFY` procedure uses the information generated by the `DIFFERENCES` procedure to rectify the two tables. Any rows found in the first table and not in the second are inserted into the second table. Any rows found in the second table and not in the first are deleted from the second table.

To restore equivalency between all copies of a replicated table, complete the following steps:

Step 1 Select one copy of the table to be the "reference" table.

This copy will be used to update all other replicas of the table as needed.

Step 2 Determine if it is necessary to check all rows and columns in the table for differences, or only a subset.

For example, it might not be necessary to check rows that have not been updated since the last time that you checked for differences. Although it is not necessary to check all columns, your column list must include all columns that make up the primary key (or that you designated as a substitute identity key) for the table.

Step 3 After determining which columns you will be checking in the table, create two tables to hold the results of the comparison.

You must create one table that can hold the data for the columns being compared. For example, if you decide to compare the `employee_id`, `salary`, and `department_id` columns of the `employees` table, then your `CREATE` statement would need to be similar to the following:

    CREATE TABLE hr.missing_rows_data (
      employee_id     NUMBER(6),
      salary          NUMBER(8,2),
      department_id   NUMBER(4));

You must also create a table that indicates where the row is found. This table must contain three columns with the datatypes shown in the following example:

    CREATE TABLE hr.missing_rows_location (
      present     VARCHAR2(128),
      absent      VARCHAR2(128),
      r_id        ROWID);

Step 4 Suspend replication activity for the replication group containing the tables that you want to compare.

Although suspending replication activity for the group is not a requirement, rectifying tables that were not quiesced first can result in inconsistencies in your data.

    CONNECT repadmin/repadmin

    BEGIN
       DBMS_REPCAT.SUSPEND_MASTER_ACTIVITY (
          gname => 'hr_repg');
    END;
    /

Step 5 At the site containing the "reference" table, call the DIFFERENCES procedure.

For example, if you wanted to compare the `employees` tables at the New York and San Francisco sites, then your procedure call would look similar to the following:

    BEGIN
       DBMS_RECTIFIER_DIFF.DIFFERENCES (
          sname1              =>   'hr',
          oname1              =>   'employees',
          reference_site      =>   'ny.world',
          sname2              =>   'hr',
          oname2              =>   'employees',
          comparison_site     =>   'mv4.world',
          where_clause        =>   '',
          column_list         =>   'employee_id,salary,department_id',
          missing_rows_sname  =>   'hr',
          missing_rows_oname1 =>   'missing_rows_data',
          missing_rows_oname2 =>   'missing_rows_location',
          missing_rows_site   =>   'ny.world',
          max_missing         =>    500,
          commit_rows         =>    50);
    END;
    /

Figure 9-1 shows an example of two replicas of the `employees` table and what the resulting missing rows tables would look like if you executed the `DIFFERENCES` procedure on these replicas.

Figure 9-1 Determining Differences Between Replicas

Description of the illustration repma003.gif

Notice that the two missing rows tables are related by the `ROWID` and `r_id` columns.

Step 6 Rectify the table at the "comparison" site to be equivalent to the table at the "reference" site.

    BEGIN

    DBMS_RECTIFIER_DIFF.RECTIFY (
          sname1              =>   'hr',
          oname1              =>   'employees',
          reference_site      =>   'ny.world',
          sname2              =>   'hr',
          oname2              =>   'employees',
          comparison_site     =>   'mv4.world',
          column_list         =>   'employee_id,salary,department_id',
          missing_rows_sname  =>   'hr',
          missing_rows_oname1 =>   'missing_rows_data',
          missing_rows_oname2 =>   'missing_rows_location',
          missing_rows_site   =>   'ny.world',
          commit_rows         =>    50);
    END;
    /

The `RECTIFY` procedure temporarily disables replication at the "comparison" site while it performs the necessary insertions and deletions, as you would not want to propagate these changes. `RECTIFY` first performs all of the necessary `DELETE` operations and then performs all of the `INSERT` operations. This ensures that there are no violations of a `PRIMARY` `KEY` constraint.

After you have successfully executed the `RECTIFY` procedure, your missing rows tables should be empty.

Caution: If you have any additional constraints on the "comparison" table, then you must ensure that they are not violated when you call `RECTIFY`. You might need to update the table directly using the information in the missing rows table. If so, then be sure to `DELETE` the appropriate rows from the missing rows tables.

Step 7 Repeat Steps 5 and 6 for the remaining copies of the replicated table.

Remember to use the same "reference" table each time to ensure that all copies are identical when you complete this procedure.

Step 8 Resume replication activity for the master group.

    BEGIN
       DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
          gname => 'hr_repg');
    END;
    /

## Managing the Deferred Transactions Queue

Typically, Advanced Replication is configured to push and purge the deferred transaction queue automatically. At times, however, you might need to push or purge the deferred transaction queue manually. The process for pushing the deferred transaction queue is the same at master sites and materialized view sites.

### Pushing the Deferred Transaction Queue``

Master sites are configured to push the deferred transaction queue automatically at set intervals. At materialized view sites, if you do not automatically propagate the transactions in your deferred transaction queue during the refresh of your materialized view, then you must complete the following steps to propagate changes made to the updatable materialized view to its master table or master materialized view.

This example illustrates pushing the deferred transaction queue at a materialized view site, but the process is the same at master sites and materialized view sites.

Executed As: Materialized View Administrator

Executed At: Materialized View Site

Complete the following steps:

Step 1 Connect to the materialized view site as the materialized view administrator.

    CONNECT mviewadmin/mviewadmin@mv1.world

Step 2 Execute the following SELECT statement to view the deferred transactions and their destinations.

Propagation of the deferred transaction queue is based on the destination of the transaction. Each distinct destination and the number of transactions pending for the destination will be displayed.

    SELECT DISTINCT(dblink), COUNT(deferred_tran_id)
       FROM deftrandest GROUP BY dblink;

Step 3 Execute the DBMS_DEFER_SYS.PUSH function for each site that is listed as a destination for a deferred transaction.

    DECLARE
       temp INTEGER;
    BEGIN
       temp := DBMS_DEFER_SYS.PUSH (
          destination => 'orc1.world',
          stop_on_error => FALSE,
          delay_seconds => 0,
          parallelism => 0);
    END;
    /

Run the `PUSH` procedure for each destination that was returned in the `SELECT` statement you ran in Step 2.

### Purging the Deferred Transaction Queue``

If your system is not set to automatically purge the successfully propagated transactions in your deferred transaction queue periodically, then you must complete the following steps to purge them manually.

This example illustrates purging the deferred transaction queue at a materialized view site, but the process is the same at master sites and materialized view sites.

Executed As: Materialized View Administrator

Executed At: Materialized View Site

Complete the following steps:

Step 1 Connect to the materialized view site as the materialized view administrator.

    CONNECT mviewadmin/mviewadmin@mv1.world

Step 2 Purge the deferred transaction queue.

    DECLARE

    temp INTEGER;
    BEGIN
       temp := DBMS_DEFER_SYS.PURGE (
          purge_method => DBMS_DEFER_SYS.PURGE_METHOD_QUICK);
    END;
    /

Note: If you use the `purge_method_quick` parameter, deferred transactions and deferred procedure calls that have been successfully pushed can remain in the `DEFTRAN` and `DEFCALL` data dictionary views for longer than expected before they are purged. See the "Usage Notes" for `DBMS_DEFER_SYS.PURGE` for details.

### Using the ANYDATA Type to Determine the Value of an Argument in a Deferred Call

If you are using column objects, collections, or `REF`s in a replicated table, then you can use the `GET_ANYDATA_ARG` function in the `DBMS_DEFER_QUERY` package to determine the value of an argument in a deferred call that involves one of these user-defined types.

The following example illustrates how to use the `GET_ANYDATA_ARG` function. This example uses the following user-defined types in the `oe` sample schema.

    CREATE TYPE phone_list_typ AS VARRAY(5) OF VARCHAR2(25);
    /

    CREATE TYPE warehouse_typ AS OBJECT
        (warehouse_id       NUMBER(3),
         warehouse_name     VARCHAR2(35),
         location_id        NUMBER(4)
        );
    /

    CREATE TYPE inventory_typ AS OBJECT
        (product_id          NUMBER(6),
         warehouse           warehouse_typ,
         quantity_on_hand    NUMBER(8)
        );
    /

    CREATE TYPE inventory_list_typ AS TABLE OF inventory_typ;
    /

The following procedure retrieves the argument value for collection, object, and `REF` instances of calls stored in the deferred transactions queue. This procedure assumes that the call number and transaction id are available.

The user who creates the procedure must have `EXECUTE` privilege on the `DBMS_DEFER_QUERY` package and must have `CREATE` `PROCEDURE` privilege. This example uses the `oe` sample schema. Therefore, to run the example, you must grant the `oe` user these privileges.

    CONNECT SYSTEM/MANAGER AS SYSDBA

    GRANT EXECUTE ON DBMS_DEFER_QUERY TO oe;

    GRANT CREATE PROCEDURE TO oe;

    CONNECT oe/oe@orc1.world

    CREATE OR REPLACE PROCEDURE get_userdef_arg AS
      call_no      NUMBER := 0;
      txn_id       VARCHAR2(128) := 'xx.xx.xx';
      anydata_val  ANYDATA;
      t            ANYTYPE;
      data_pl      phone_list_typ;     -- varray
      data_ntt     inventory_list_typ; -- nested table type
      data_p       warehouse_typ;      -- object type
      ref1         REF inventory_typ;  -- REF type
      rval         PLS_INTEGER;        -- return value
      tc           PLS_INTEGER;        -- return value
      prec         PLS_INTEGER;        -- precision
      scale        PLS_INTEGER;        -- scale
      len          PLS_INTEGER;        -- length
      csid         PLS_INTEGER;        -- character set id
      csfrm        PLS_INTEGER;        -- character set form
      cnt          PLS_INTEGER;        -- count of varray elements or number of
                                       -- object attributes
      sname        VARCHAR2(35);       -- schema name
      type_name    VARCHAR2(35);       -- type name
      version      VARCHAR2(35);
    BEGIN
       FOR i IN 1 .. 5 LOOP
         anydata_val := DBMS_DEFER_QUERY.GET_ANYDATA_ARG(call_no, i, txn_id);
         -- Get the type information, including type name.
         tc := anydata_val.GetType(t);
         tc := t.GetInfo(prec, scale, len, csid, csfrm, sname, type_name,
                         version, cnt);
         -- Based on the type name, convert the anydata value to the appropriate
         -- user-defined types.
         IF type_name = 'PHONE_LIST_TYP' THEN
           -- The anydata_val contains phone_list_typ varray instance.
           rval := anydata_val.GetCollection(data_pl);
           -- Do something with data_pl.
         ELSIF type_name = 'INVENTORY_LIST_TYP' THEN
           -- anydata_val contains inventory_list_typ nested table instance.
           rval := anydata_val.GetCollection(data_ntt);
           -- Do something with data_ntt.
         ELSIF type_name = 'WAREHOUSE_TYP' THEN
           -- The anydata_val contains warehouse_typ object instance.
           rval := anydata_val.GetObject(data_p);
           -- Do something with data_p.
         ELSIF type_name = 'INVENTORY_TYP' THEN
           -- The anydata_val contains a reference to inventory_typ object instance.
           rval := anydata_val.GetRef(ref1);
           -- Do something with ref1.
         END IF;
       END LOOP;
    END;
    /

See Also: "GET_datatype_ARG Function" Oracle Database SQL Reference, Oracle Database Application Developer's Guide - Object-Relational Features, and Oracle Database PL/SQL Packages and Types Reference for more information about the `ANYDATA` datatype

## Managing the Error Queue

As an administrator of a replication environment, you should regularly monitor the error queue to determine if any deferred transactions were not successfully applied at the target master site.

To check the error queue, issue the following `SELECT` statement (as the replication administrator) when connected to the target master site:

    SELECT * FROM deferror;

If the error queue contains errors, then you should resolve the error condition and reexecute the deferred transaction. You have two options when reexecuting a deferred transaction: you can reexecute in the security context of the user who received the deferred transaction, or you can reexecute the deferred transaction with an alternate security context.

Caution: If you have multiple error transactions and you want to make sure they are reexecuted in the correct order, then you can specify `NULL` for the `deferred_tran_id` parameter in the procedures in the following sections. If you do not specify `NULL`, then reexecuting individual transactions in the wrong order can cause conflicts.

### Reexecuting Error Transaction as the Receiver``

The following procedure reexecutes a specified deferred transaction in the security context of the user who received the deferred transaction. This procedure should not be executed until the error situation has been resolved.

Meet the following requirements to complete these actions:

Executed As: Replication Administrator

Executed At: Site Containing Errors

Replication Status: Normal

Complete the following steps:

Step 1 Connect to the master site as the replication administrator.

    CONNECT repadmin/repadmin@orc2.world

Step 2 Reexecute the error transaction.

    BEGIN

    DBMS_DEFER_SYS.EXECUTE_ERROR (
          deferred_tran_id => '1.12.2904',
          destination => 'ORC2.WORLD');
    END;
    /

### Reexecuting Error Transaction as Alternate User``

The following procedure reexecutes a specified deferred transaction in the security context of the currently connected user. This procedure should not be executed until the error situation has been resolved.

Meet the following requirements to complete these actions:

Executed As: Connected User

Executed At: Site Containing Errors

Replication Status: Normal

Complete the following steps:

Step 1 Connect to the master site as the alternate user.

    CONNECT hr/hr@orc2.world

Step 2 Reexecute the error transaction.

    BEGIN

    DBMS_DEFER_SYS.EXECUTE_ERROR_AS_USER (
          deferred_tran_id => '1.12.2904',
          destination => 'ORC2.WORLD');
    END;
    /